-- @owner: @peilinqian
-- @date: 2024/10/22
-- @testpoint: 触发器为instead of，触发事件为insert触发器,使用show triggers查询

@conn PrimaryDbBmode;

--step1：创建表 expect：成功
create table t_compatibility_grammar_show_case0008(id1 int, id2 int, id3 int);
create view v_compatibility_grammar_show_case0008 as select * from t_compatibility_grammar_show_case0008 where id1<10;

--step2：创建触发器函数 expect：成功
create or replace function f_compatibility_grammar_show_case0008() returns trigger as
$$
declare
begin
    insert into t_compatibility_grammar_show_case0008 values(new.id1, new.id2, new.id3);
 return new;
end
$$ language plpgsql;
/

--step3：创建创建触发器 expect：成功
create trigger trigger_compatibility_grammar_show_case0008 instead of insert on v_compatibility_grammar_show_case0008 for each row execute procedure f_compatibility_grammar_show_case0008();
/

--step4：执行insert触发事件 expect：成功
insert into t_compatibility_grammar_show_case0008 values(1,2,3);
insert into t_compatibility_grammar_show_case0008 values(5,6,7);
insert into t_compatibility_grammar_show_case0008 values(11,22,33);
insert into t_compatibility_grammar_show_case0008 values(55,66,77);
--检查触发结果
select * from t_compatibility_grammar_show_case0008;
select * from v_compatibility_grammar_show_case0008;

--step5：查询触发器信息不加可选项 expect：显示结果正确
show triggers ;

--step6：查询触发器信息from schema； expect：显示结果正确
show triggers from public;

--step7：查询触发器信息添加where条件； expect：显示结果正确
--查询触发器，添加where条件
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0008';
--查询触发器，添加like条件
show triggers from public where "Trigger" like '%grammar_show_case0008';

--step8：清理数据； expect：显示结果正确
drop trigger trigger_compatibility_grammar_show_case0008 on v_compatibility_grammar_show_case0008;
drop function f_compatibility_grammar_show_case0008();
drop view v_compatibility_grammar_show_case0008;
drop table t_compatibility_grammar_show_case0008;
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0008';